package com.export.service.dynamic;

import com.export.entity.ReportParam;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

/**
 * 账号报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class AccountReportDynamicStatement {

    //**********************************账号报表*************************************//



    /**
     * 账户报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String accountReportExportSql(ReportParam reportParam) {
        String sql = "SELECT a.aut_keyid,o.org_type," +
                "CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id,\n" +
                "MAX(c.cust_sname) AS cust_sname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_null_num) AS stat_null_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o on o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c on c.cust_id = o.org_cust_id\n" +
                "LEFT JOIN sys.sys_authon a on sta.stat_org_id = a.aut_org_id WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.accountReportConditions(reportParam);
        sql += " GROUP BY c.cust_id,o.org_type,a.aut_keyid ";
        return sql;
    }




    /**
     * 账户报告条件
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    private String accountReportConditions(ReportParam reportParam) {
        String sql = "";
        if (StringUtils.isNotBlank(reportParam.getAutKeyid())) {
            sql += " AND aut_keyid LIKE #{%autKeyid%}";
        }
        if (StringUtils.isNotBlank(reportParam.getCustId())) {
            sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) LIKE #{%custId%}";
        }
        return sql;
    }





    /**
     * 导出账户报告详细列表
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String accountReportDetailListExport(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        return "SELECT TO_CHAR(stat_date,'" + grouping + "') AS stat_date,MAX(a.aut_keyid) AS aut_keyid,CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id,\n" +
                " o.org_type,MAX(c.cust_sname) AS cust_sname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o on o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c on c.cust_id = o.org_cust_id\n" +
                "LEFT JOIN sys.sys_authon a on sta.stat_org_id = a.aut_org_id" +
                " WHERE aut_keyid = #{autKeyid} AND stat_date BETWEEN #{startDate} AND #{endDate}" +
                " GROUP BY TO_CHAR(stat_date,'" + grouping + "') ,cust_id,cust_name,o.org_type";
    }





}
